/* OPTIONS OBS=500 NOREPLACE  ; */  

%include "ASMimplibs.sas";

/* Construct "concrete demand density" (construction sector employment per square mile) 
   measures at the Component Economic Area (CEA) level.

	input files:
		ccdb.city_county_07_areas.csv
		bea.BEAeastructure.csv
		cbp.constr_emp02
		cbp.County_Construction_Employment_2007_raw.csv

	output files (SAS datasets):
		concrete.CEA_cou_area_emp02
		concrete.CEA_cou_area_emp07
**/

/** 
    Import 2007 City County areas,  and BEA's Component Economic Area (CEA) file and
    merge by county with construction sector employment from 2002 and 2007 County Business Patterns.
    Compute "concrete demand density" (construction sector employment divided by county area) for 2002 and 2007.
**/


/** Import 2007 City/County areas **/

PROC IMPORT OUT=city_cou_07_areas  
  DATAFILE= "city_county_07_areas.csv" 
  DBMS=CSV REPLACE;
  GETNAMES=YES;
  DATAROW=2; 
RUN;

/** Import BEA's CEA/FIPS/County file. **/

PROC IMPORT OUT=BEAeastructure  
  DATAFILE= "BEAeastructure.csv" 
  DBMS=CSV REPLACE;
  GETNAMES=YES;
  DATAROW=2; 
RUN;


/** Merge BEA CEA file with City/County area file **/

proc sort data=city_cou_07_areas;
 by County_FIPS_Code;
run;

proc sort data=BEAeastructure;
 by County_FIPS_Code;
run;

data CEA_county_area;
 merge city_cou_07_areas (in=inarea) BEAeastructure (in=inbea);
by County_FIPS_Code;
if inarea and inbea;
run;

/** Merge CEA/area file with 2002 county construction employment file from County Business Patterns. **/

data CEA_county_area_for02 (keep = FIPSST COU area BEA_CEA_Code);
 set CEA_county_area;
 FIPSSTCOU_char = put(County_FIPS_Code,5.);
 if substr(FIPSSTCOU_char,1,1)=" "  /* For some states, leading zero was dropped when I exported to .csv format. */ 
 then do;
   State = substr(FIPSSTCOU_char,2,1);
   FIPSST = cat("0",State);
   COU = substr(FIPSSTCOU_char,3,3);
 end;
 else do;
   FIPSST = substr(FIPSSTCOU_char,1,2);
   COU = substr(FIPSSTCOU_char,3,3);
 end;
run;

proc sort data=CEA_county_area_for02;
 by FIPSST COU;
run;

data constr_emp02;
 set cbp.constr_emp02;
run;

proc sort data=constr_emp02;
 by FIPSST COU;
run;

data CEA_cou_area_emp02 rej_cbp02 rej_cea_area02;
 merge constr_emp02 (in=incbp02) CEA_county_area_for02 (in=incea);
by FIPSST COU;
if incbp02 and incea then output CEA_cou_area_emp02;
else if incbp02 then output rej_cbp02;
else output rej_cea_area02;
run; 


/** Aggregate 2002 county construction sector employment and area to the CEA level. */
proc sort data=CEA_cou_area_emp02;
 by BEA_CEA_Code;
run;


proc means data=CEA_cou_area_emp02 SUM NOPRINT;
 var constr_emps area;
by BEA_CEA_Code;
output out=CEA_tots02 sum(constr_emps)=constr_emps02_CEA sum(area)=CEA_area;
run;

data CEA_tots02 (drop = _TYPE_ _FREQ_);
 set CEA_tots02; 
run;

/** Merge the 2002 CEA totals back with the county-level data and save the file: */
data concrete.CEA_cou_area_emp02;
 merge CEA_cou_area_emp02 CEA_tots02;
 by BEA_CEA_Code;
 CEA_ddensity = constr_emps02_CEA/CEA_area;
run; 
 
/*******************************************************/
/** Import 2007 county construction employment file. **/

PROC IMPORT OUT=constr_emp07  
DATAFILE= "County_Construction_Employment_2007_raw.csv" 
  DBMS=CSV REPLACE;
  GETNAMES=YES;
  DATAROW=2; 
RUN;


/** Merge 2007 county construction employment data with merged file */

proc sort data=constr_emp07;
 by County_Name_ST_CODE;
run;


proc sort data=CEA_county_area;
 by County_Name_ST_CODE;
run;

/* A few counties in the City County Data Book and BEA CEA data are 
  not in the CBP data.  All of these are counties with very small
  populations, so presumably these counties have no construction sector employees. */

data CEA_cou_area_emp07 rej_emp07;
 merge constr_emp07 (in=inemp) CEA_county_area (in=incea);
by County_Name_ST_CODE;
 if Number_of_construction_employee = . then Number_of_construction_employee=0;
 if incea then output CEA_cou_area_emp07;
 else if inemp then output rej_emp07;
run;


proc datasets library=work;
 modify CEA_cou_area_emp07;
 rename Number_of_construction_employee=constr_emps;
run;

/** Aggregate county construction sector employment and area to the CEA level. */
proc sort data=CEA_cou_area_emp07;
 by BEA_CEA_Code;
run;


proc means data=CEA_cou_area_emp07 SUM NOPRINT;
 var constr_emps area;
by BEA_CEA_Code;
output out=CEA_tots sum(constr_emps)=constr_emps_CEA sum(area)=CEA_area;
run;

data CEA_tots (drop = _TYPE_ _FREQ_);
 set CEA_tots; 
run;

/** Merge the CEA totals back with the county-level data: */
data CEA_cou_area_emp07;
 merge CEA_cou_area_emp07 CEA_tots;
 by BEA_CEA_Code;
 CEA_ddensity = constr_emps_CEA/CEA_area;
run; 


data concrete.CEA_cou_area_emp07 (keep = FIPSST COU CEA_ddensity BEA_CEA_Code);
 set CEA_cou_area_emp07;
 FIPSSTCOU_char = put(County_FIPS_Code,5.);
 if substr(FIPSSTCOU_char,1,1)=" "  /* For some states, leading zero was dropped when I exported to .csv format. */ 
 then do;
   State = substr(FIPSSTCOU_char,2,1);
   FIPSST = cat("0",State);
   COU = substr(FIPSSTCOU_char,3,3);
 end;
 else do;
   FIPSST = substr(FIPSSTCOU_char,1,2);
   COU = substr(FIPSSTCOU_char,3,3);
 end;
run;

